package com.example.liu;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class ContextServlrtTest extends HttpServlet {
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException{
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        ServletContext context = this.getServletContext();
        InputStream stream = context.getResourceAsStream("/WEB-INF/classes/jdbc.properties");
        Properties properties = new Properties();
        properties.load(stream);
        String dbName = properties.getProperty("dbName");
        String username = properties.getProperty("userName");
        String userPasswd = properties.getProperty("userPasswd");
        String url = properties.getProperty("url");
        url = url +dbName + "?user=" +username + "&password=" + userPasswd+"&useUnicode=true&characterEncoding=utf-8";
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(url);
            Statement statement = conn.createStatement();
            String sql = "select * from student ORDER BY stu_speciality DESC,stu_politicalstatus ASC";
            ResultSet rs = statement.executeQuery(sql);
            out.println("<table border=1>");
            out.println("<th colspan=7 style= 'font-size: x-large; alignment : center'>学生信息表</th>");
            out.println("<tr style='text-align:center'>");
            out.println("<td>学号</td><td>姓名</td><td>性别</td><td>政治身份</td>\n" +
                    "<td>出生日期</td><td>身份证号</td><td>所在系部</td> </tr>");
            while (rs.next()){
                out.println("<tr style='text-align:center'>");
                out.println("<td>" + rs.getString(1)+"</td>");
                out.println("<td>" + rs.getString(2)+"</td>");
                out.println("<td>" + rs.getString(3)+"</td>");
                out.println("<td>" + rs.getString("stu_politicalstatus")+"</td>");
                out.println("<td>" + rs.getString("stu_birthday")+"</td>");
                out.println("<td>" + rs.getString("stu_identitycard")+"</td>");
                out.println("<td>" + rs.getString("stu_speciality")+"</td>");
                out.println("</ty>");
            }
            out.println("</table></body>");
        }catch (Exception e) {
            e.printStackTrace();
        }
        out.flush();
        out.close();
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException,IOException{
        doPost(request,response);
    }
}
